package com.unittec.zk.provider.excel.export;

import cn.hutool.core.io.FileUtil;
import com.unittec.zk.provider.utils.OrderNumUtils;
import com.unittec.zk.provider.utils.OssUtils;
import com.unittec.zk.provider.utils.UUIDUtils;
import com.unittec.zk.sdk.root.utils.DateUtiles;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * 导出Excel
 *
 * @param <T>
 * @author liuyazhuang
 */
public class ExportExcelUtil<T> {

    private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtil.class);

    //导出条数常量
    public final static Long EXPORT_NUMS = 100L;

    // 2007 版本以上 最大支持1048576行
    public final static String EXCEl_FILE_2007 = "2007";
    // 2003 版本 最大支持65536 行
    public final static String EXCEL_FILE_2003 = "2003";

    /**
     * <p>
     * 导出无头部标题行Excel <br>
     * 时间格式默认：yyyy-MM-dd hh:mm:ss <br>
     * </p>
     *
     * @param title   表格标题
     * @param dataSet 数据集合
     * @param version 2003 或者 2007，不传时默认生成2003版本
     */
    public String exportExcel(String title, Collection<T> dataSet, String version) {
        if (StringUtils.isEmpty(version) || EXCEL_FILE_2003.equals(version.trim())) {
            return exportExcel2003(title, null, dataSet, "yyyy-MM-dd HH:mm:ss");
        } else {
            return exportExcel2007(title, null, dataSet, "yyyy-MM-dd HH:mm:ss");
        }
    }

    /**
     * <p>
     * 导出带有头部标题行的Excel <br>
     * 时间格式默认：yyyy-MM-dd hh:mm:ss <br>
     * </p>
     *
     * @param title   表格标题
     * @param headers 头部标题集合
     * @param dataSet 数据集合
     * @param version 2003 或者 2007，不传时默认生成2003版本
     */
    public String exportExcel(String title, List<ExportExcelColumn> headers, Collection<T> dataSet, String version) {
        if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) {
            return exportExcel2003(title, headers, dataSet, "yyyy-MM-dd HH:mm:ss");
        } else {
            return exportExcel2007(title, headers, dataSet, "yyyy-MM-dd HH:mm:ss");
        }
    }

    /**
     * <p>
     * 导出带有头部标题行的Excel <br>
     * 时间格式默认：yyyy-MM-dd hh:mm:ss <br>
     * </p>
     *
     * @param title   表格标题
     * @param headers 头部标题集合
     * @param dataSet 数据集合
     * @param version 2003 或者 2007，不传时默认生成2003版本
     */
    public String exportExcelForMap(String title, List<ExportExcelColumn> headers, Collection<Map<String, Object>> dataSet, String version) {
        if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) {
            return exportExcel2003ForMap(title, headers, dataSet, "yyyy-MM-dd HH:mm:ss");
        } else {
            return exportExcel2007ForMap(title, headers, dataSet, "yyyy-MM-dd HH:mm:ss");
        }
    }

    /**
     * <p>
     * 导出带有头部标题行的Excel <br>
     * 时间格式默认：yyyy-MM-dd hh:mm:ss <br>
     * </p>
     *
     * @param title   表格标题
     * @param headers 头部标题集合
     * @param dataSet 数据集合
     * @param version 2003 或者 2007，不传时默认生成2003版本
     */
    public void exportExcelForMap(String title, List<ExportExcelColumn> headers, Collection<Map<String, Object>> dataSet, String version, HttpServletResponse response) {
        if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) {
            exportExcel2003ForMap(title, headers, dataSet, "yyyy-MM-dd HH:mm:ss", response);
        } else {
            exportExcel2007ForMap(title, headers, dataSet, "yyyy-MM-dd HH:mm:ss", response);
        }
    }

    /**
     * <p>
     * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>
     * 此版本生成2007以上版本的文件 (文件后缀：xlsx)
     * </p>
     *
     * @param title   表格标题名
     * @param headers 表格头部标题集合
     * @param dataSet 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的JavaBean属性的数据类型有基本数据类型及String,Date
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public String exportExcel2007(String title, List<ExportExcelColumn> headers, Collection<T> dataSet, String pattern) {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(20);
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setColor(IndexedColors.BLACK.index);
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        XSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        XSSFFont font2 = workbook.createFont();
        //font2.setBold(true);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 产生表格标题行
        XSSFRow row = sheet.createRow(0);
        XSSFCell cellHeader;
        int headerNum = 0;
        for (ExportExcelColumn exportExcelColumn : headers) {
            cellHeader = row.createCell(headerNum);
            cellHeader.setCellStyle(style);
            cellHeader.setCellValue(new XSSFRichTextString(exportExcelColumn.getFieldNameValue()));
            headerNum++;
        }
        // 遍历集合数据，产生数据行
        Iterator<T> it = dataSet.iterator();
        int index = 0;
        T t;
        XSSFRichTextString richString;
        String regexStr = "^//d+(//.//d+)?$";
        Pattern p = Pattern.compile(regexStr);
        Matcher matcher;
        String fieldName;
        String getMethodName;
        XSSFCell cell;
        Class tCls;
        Method getMethod;
        Object value;
        String textValue;
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            t = (T) it.next();
            // 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值
            for (int i = 0; i < headers.size(); i++) {
                ExportExcelColumn exportExcelColumn = headers.get(i);
                cell = row.createCell(i);
                cell.setCellStyle(style2);
                fieldName = exportExcelColumn.getFieldName();
                getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                try {
                    tCls = t.getClass();
                    getMethod = tCls.getMethod(getMethodName, new Class[]{});
                    value = getMethod.invoke(t, new Object[]{});
                    // 判断值的类型后进行强制类型转换
                    textValue = null;
                    if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof Float) {
                        textValue = String.valueOf((Float) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Double) {
                        textValue = String.valueOf((Double) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Long) {
                        cell.setCellValue((Long) value);
                    }
                    if (value instanceof Boolean) {
                        textValue = "是";
                        if (!(Boolean) value) {
                            textValue = "否";
                        }
                    } else if (value instanceof Date) {
                        textValue = sdf.format((Date) value);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        if (value != null) {
                            textValue = value.toString();
                        }
                    }
                    if (textValue != null) {
                        matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            richString = new XSSFRichTextString(textValue);
                            cell.setCellValue(richString);
                        }
                    }
                } catch (SecurityException e) {
                    e.printStackTrace();
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                } finally {
                    // 清理资源
                }
            }
        }
        try {
            File file = new File(System.getProperty("user.dir") + "/" + UUIDUtils.getId() + ".xlsx");
            FileOutputStream outputStream = new FileOutputStream(file);
            workbook.write(outputStream);
            String uploadKey = "excelExport/" + DateUtiles.format(new Date(), "yyyy/MM/dd") + "/temp/" + UUIDUtils.getId() + ".xlsx";
            OssUtils.uploadObject2Oss(uploadKey, OrderNumUtils.createOrderNum() + ".xlsx", new FileInputStream(file));
            outputStream.close();
            FileUtil.del(file);
            return uploadKey;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * <p>
     * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>
     * 此版本生成2007以上版本的文件 (文件后缀：xlsx)
     * </p>
     *
     * @param title   表格标题名
     * @param headers 表格头部标题集合
     * @param dataSet 需要显示的数据集合
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public String exportExcel2007ForMap(String title, List<ExportExcelColumn> headers, Collection<Map<String, Object>> dataSet, String pattern) {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(20);
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setColor(IndexedColors.BLACK.index);
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        XSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        XSSFFont font2 = workbook.createFont();
        //font2.setBold(true);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 产生表格标题行
        XSSFRow row = sheet.createRow(0);
        XSSFCell cellHeader;
        int headerNum = 0;
        for (ExportExcelColumn exportExcelColumn : headers) {
            cellHeader = row.createCell(headerNum);
            cellHeader.setCellStyle(style);
            cellHeader.setCellValue(new XSSFRichTextString(exportExcelColumn.getFieldNameValue()));
            headerNum++;
        }
        // 遍历集合数据，产生数据行
        int index = 0;
        XSSFRichTextString richString;
        String regexStr = "^//d+(//.//d+)?$";
        Pattern p = Pattern.compile(regexStr);
        Matcher matcher;
        String fieldName;
        XSSFCell cell;
        Object value;
        String textValue;
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        for (Map<String, Object> map : dataSet) {
            index++;
            row = sheet.createRow(index);
            for (int i = 0; i < headers.size(); i++) {
                ExportExcelColumn exportExcelColumn = headers.get(i);
                cell = row.createCell(i);
                cell.setCellStyle(style2);
                fieldName = exportExcelColumn.getFieldName();
                try {
                    value = map.get(fieldName);
                    if (exportExcelColumn.isConversion()) {
                        value = exportExcelColumn.getConversionMap().get(value);
                    }
                    // 判断值的类型后进行强制类型转换
                    textValue = null;
                    if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof Float) {
                        textValue = String.valueOf((Float) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Double) {
                        textValue = String.valueOf((Double) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Long) {
                        cell.setCellValue((Long) value);
                    }
                    if (value instanceof Boolean) {
                        textValue = "是";
                        if (!(Boolean) value) {
                            textValue = "否";
                        }
                    } else if (value instanceof Date) {
                        textValue = sdf.format((Date) value);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        if (value != null) {
                            textValue = value.toString();
                        }
                    }
                    if (textValue != null) {
                        matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            richString = new XSSFRichTextString(textValue);
                            cell.setCellValue(richString);
                        }
                    }
                } catch (SecurityException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } finally {
                    // 清理资源
                }
            }
        }
        try {
            File file = new File(System.getProperty("user.dir") + "/" + UUIDUtils.getId() + ".xlsx");
            FileOutputStream outputStream = new FileOutputStream(file);
            workbook.write(outputStream);
            String uploadKey = "excelExport/" + DateUtiles.format(new Date(), "yyyy/MM/dd") + "/temp/" + UUIDUtils.getId() + ".xlsx";
            OssUtils.uploadObject2Oss(uploadKey, OrderNumUtils.createOrderNum() + ".xlsx", new FileInputStream(file));
            outputStream.close();
            FileUtil.del(file);
            return uploadKey;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * <p>
     * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>
     * 此版本生成2007以上版本的文件 (文件后缀：xlsx)
     * </p>
     *
     * @param title   表格标题名
     * @param headers 表格头部标题集合
     * @param dataSet 需要显示的数据集合
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public void exportExcel2007ForMap(String title, List<ExportExcelColumn> headers, Collection<Map<String, Object>> dataSet, String pattern, HttpServletResponse response) {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(20);
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setColor(IndexedColors.BLACK.index);
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        XSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        XSSFFont font2 = workbook.createFont();
        //font2.setBold(true);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 产生表格标题行
        XSSFRow row = sheet.createRow(0);
        XSSFCell cellHeader;
        int headerNum = 0;
        for (ExportExcelColumn exportExcelColumn : headers) {
            cellHeader = row.createCell(headerNum);
            cellHeader.setCellStyle(style);
            cellHeader.setCellValue(new XSSFRichTextString(exportExcelColumn.getFieldNameValue()));
            headerNum++;
        }
        // 遍历集合数据，产生数据行
        int index = 0;
        XSSFRichTextString richString;
        String regexStr = "^//d+(//.//d+)?$";
        Pattern p = Pattern.compile(regexStr);
        Matcher matcher;
        String fieldName;
        XSSFCell cell;
        Object value;
        String textValue;
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        for (Map<String, Object> map : dataSet) {
            index++;
            row = sheet.createRow(index);
            for (int i = 0; i < headers.size(); i++) {
                ExportExcelColumn exportExcelColumn = headers.get(i);
                cell = row.createCell(i);
                cell.setCellStyle(style2);
                fieldName = exportExcelColumn.getFieldName();
                try {
                    value = map.get(fieldName);
                    if (exportExcelColumn.isConversion()) {
                        value = exportExcelColumn.getConversionMap().get(value);
                    }
                    // 判断值的类型后进行强制类型转换
                    textValue = null;
                    if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof Float) {
                        textValue = String.valueOf((Float) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Double) {
                        textValue = String.valueOf((Double) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Long) {
                        cell.setCellValue((Long) value);
                    }
                    if (value instanceof Boolean) {
                        textValue = "是";
                        if (!(Boolean) value) {
                            textValue = "否";
                        }
                    } else if (value instanceof Date) {
                        textValue = sdf.format((Date) value);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        if (value != null) {
                            textValue = value.toString();
                        }
                    }
                    if (textValue != null) {
                        matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            richString = new XSSFRichTextString(textValue);
                            cell.setCellValue(richString);
                        }
                    }
                } catch (SecurityException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } finally {
                    // 清理资源
                }
            }
        }
        try {
            //将文件输出
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * <p>
     * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>
     * 此方法生成2003版本的excel,文件名后缀：xls <br>
     * </p>
     *
     * @param title   表格标题名
     * @param headers 表格头部标题集合
     * @param dataSet 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的JavaBean属性的数据类型有基本数据类型及String,Date
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public String exportExcel2003(String title, List<ExportExcelColumn> headers, Collection<T> dataSet, String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setColor(IndexedColors.BLACK.index);
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        //字体加粗
        //font2.setBold(true);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        HSSFCell cellHeader;
        int headerNum = 0;
        for (ExportExcelColumn exportExcelColumn : headers) {
            cellHeader = row.createCell(headerNum);
            cellHeader.setCellStyle(style);
            cellHeader.setCellValue(new HSSFRichTextString(exportExcelColumn.getFieldNameValue()));
            headerNum++;
        }
        // 遍历集合数据，产生数据行
        Iterator<T> it = dataSet.iterator();
        int index = 0;
        T t;
        HSSFRichTextString richString;
        String regexStr = "^//d+(//.//d+)?$";
        Pattern p = Pattern.compile(regexStr);
        Matcher matcher;
        String fieldName;
        String getMethodName;
        HSSFCell cell;
        Class tCls;
        Method getMethod;
        Object value;
        String textValue;
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            t = (T) it.next();
            // 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值
            for (int i = 0; i < headers.size(); i++) {
                ExportExcelColumn exportExcelColumn = headers.get(i);
                cell = row.createCell(i);
                cell.setCellStyle(style2);
                fieldName = exportExcelColumn.getFieldName();
                getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                try {
                    tCls = t.getClass();
                    getMethod = tCls.getMethod(getMethodName, new Class[]{});
                    value = getMethod.invoke(t, new Object[]{});
                    // 判断值的类型后进行强制类型转换
                    textValue = null;
                    if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof Float) {
                        textValue = String.valueOf((Float) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Double) {
                        textValue = String.valueOf((Double) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Long) {
                        cell.setCellValue((Long) value);
                    }
                    if (value instanceof Boolean) {
                        textValue = "是";
                        if (!(Boolean) value) {
                            textValue = "否";
                        }
                    } else if (value instanceof Date) {
                        textValue = sdf.format((Date) value);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        if (value != null) {
                            textValue = value.toString();
                        }
                    }
                    if (textValue != null) {
                        matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            richString = new HSSFRichTextString(textValue);
                            cell.setCellValue(richString);
                        }
                    }
                } catch (SecurityException e) {
                    e.printStackTrace();
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                } finally {
                    // 清理资源
                }
            }
        }
        try {
            File file = new File(System.getProperty("user.dir") + "/" + UUIDUtils.getId() + ".xls");
            FileOutputStream outputStream = new FileOutputStream(file);
            workbook.write(outputStream);
            String uploadKey = "excelExport/" + DateUtiles.format(new Date(), "yyyy/MM/dd") + "/temp/" + UUIDUtils.getId() + ".xls";
            OssUtils.uploadObject2Oss(uploadKey, OrderNumUtils.createOrderNum() + ".xls", new FileInputStream(file));
            outputStream.close();
            FileUtil.del(file);
            return uploadKey;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * <p>
     * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>
     * 此方法生成2003版本的excel,文件名后缀：xls <br>
     * </p>
     *
     * @param title   表格标题名
     * @param headers 表格头部标题集合
     * @param dataSet 需要显示的数据集合
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public String exportExcel2003ForMap(String title, List<ExportExcelColumn> headers, Collection<Map<String, Object>> dataSet, String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setColor(IndexedColors.BLACK.index);
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        // 字体加粗
        //font2.setBold(true);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        HSSFCell cellHeader;
        int headerNum = 0;
        for (ExportExcelColumn exportExcelColumn : headers) {
            cellHeader = row.createCell(headerNum);
            cellHeader.setCellStyle(style);
            cellHeader.setCellValue(new HSSFRichTextString(exportExcelColumn.getFieldNameValue()));
            headerNum++;
        }
        // 遍历集合数据，产生数据行
        int index = 0;
        HSSFRichTextString richString;
        String regexStr = "^//d+(//.//d+)?$";
        Pattern p = Pattern.compile(regexStr);
        Matcher matcher;
        String fieldName;
        HSSFCell cell;
        Object value;
        String textValue;
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        for (Map<String, Object> map : dataSet) {
            index++;
            row = sheet.createRow(index);
            for (int i = 0; i < headers.size(); i++) {
                ExportExcelColumn exportExcelColumn = headers.get(i);
                cell = row.createCell(i);
                cell.setCellStyle(style2);
                fieldName = exportExcelColumn.getFieldName();
                try {
                    value = map.get(fieldName);
                    if (exportExcelColumn.isConversion()) {
                        value = exportExcelColumn.getConversionMap().get(value);
                    }
                    // 判断值的类型后进行强制类型转换
                    textValue = null;
                    if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof Float) {
                        textValue = String.valueOf((Float) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Double) {
                        textValue = String.valueOf((Double) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Long) {
                        cell.setCellValue((Long) value);
                    }
                    if (value instanceof Boolean) {
                        textValue = "是";
                        if (!(Boolean) value) {
                            textValue = "否";
                        }
                    } else if (value instanceof Date) {
                        textValue = sdf.format((Date) value);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        if (value != null) {
                            textValue = value.toString();
                        }
                    }
                    if (textValue != null) {
                        matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            richString = new HSSFRichTextString(textValue);
                            cell.setCellValue(richString);
                        }
                    }
                } catch (SecurityException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } finally {
                    // 清理资源
                }
            }
        }
        try {
            File file = new File(System.getProperty("user.dir") + "/" + UUIDUtils.getId() + ".xls");
            FileOutputStream outputStream = new FileOutputStream(file);
            workbook.write(outputStream);
            String uploadKey = "excelExport/" + DateUtiles.format(new Date(), "yyyy/MM/dd") + "/temp/" + UUIDUtils.getId() + ".xls";
            OssUtils.uploadObject2Oss(uploadKey, OrderNumUtils.createOrderNum() + ".xls", new FileInputStream(file));
            outputStream.close();
            FileUtil.del(file);
            return uploadKey;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * <p>
     * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>
     * 此方法生成2003版本的excel,文件名后缀：xls <br>
     * </p>
     *
     * @param title   表格标题名
     * @param headers 表格头部标题集合
     * @param dataSet 需要显示的数据集合
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public void exportExcel2003ForMap(String title, List<ExportExcelColumn> headers, Collection<Map<String, Object>> dataSet, String pattern, HttpServletResponse response) {
        // 设置response头信息
        response.reset();
        // 改成输出excel文件
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment; filename=" + OrderNumUtils.createOrderNum() + ".xls");
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setColor(IndexedColors.BLACK.index);
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        // 字体加粗
        //font2.setBold(true);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        HSSFCell cellHeader;
        int headerNum = 0;
        for (ExportExcelColumn exportExcelColumn : headers) {
            cellHeader = row.createCell(headerNum);
            cellHeader.setCellStyle(style);
            cellHeader.setCellValue(new HSSFRichTextString(exportExcelColumn.getFieldNameValue()));
            headerNum++;
        }
        // 遍历集合数据，产生数据行
        int index = 0;
        HSSFRichTextString richString;
        String regexStr = "^//d+(//.//d+)?$";
        Pattern p = Pattern.compile(regexStr);
        Matcher matcher;
        String fieldName;
        HSSFCell cell;
        Object value;
        String textValue;
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        for (Map<String, Object> map : dataSet) {
            index++;
            row = sheet.createRow(index);
            for (int i = 0; i < headers.size(); i++) {
                ExportExcelColumn exportExcelColumn = headers.get(i);
                cell = row.createCell(i);
                cell.setCellStyle(style2);
                fieldName = exportExcelColumn.getFieldName();
                try {
                    value = map.get(fieldName);
                    if (exportExcelColumn.isConversion()) {
                        value = exportExcelColumn.getConversionMap().get(value);
                    }
                    // 判断值的类型后进行强制类型转换
                    textValue = null;
                    if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof Float) {
                        textValue = String.valueOf((Float) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Double) {
                        textValue = String.valueOf((Double) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Long) {
                        cell.setCellValue((Long) value);
                    }
                    if (value instanceof Boolean) {
                        textValue = "是";
                        if (!(Boolean) value) {
                            textValue = "否";
                        }
                    } else if (value instanceof Date) {
                        textValue = sdf.format((Date) value);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        if (value != null) {
                            textValue = value.toString();
                        }
                    }
                    if (textValue != null) {
                        matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            richString = new HSSFRichTextString(textValue);
                            cell.setCellValue(richString);
                        }
                    }
                } catch (SecurityException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } finally {

                }
            }
        }
        try {
            //将文件输出
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
